﻿/******************************************************   
创建者: ljj
创建时间: 2018-01-02
修改:
修改时间: 
功能: 单品有效计算
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using george.Web.Helper;
using NHibernateVertica;
using InventoryToPredictAPI.model;

namespace InventoryToPredictAPI
{
    [Description("单品有效值计算")]
    public class GoodsSingleValidCalcHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2018-01-02
        [MustLogin]
        [Description("单品有效计算货号信息")]
        [ReturnType(typeof(PaperData<GoodsSingleValidCalcMatInfoModel.ShowModel>))]
        public ApiResult GetGoodsSingleValidCalcMatInfoList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("大类")] string KindID = "", [ParamDescription("子项目")] string ItemOrSub = "",
            [ParamDescription("年份")] string YearNo = "", [ParamDescription("季节")] string SeasonID = "",
            [ParamDescription("性别")] string SexID = "", [ParamDescription("货号")] string MaterialID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var kindIDStr = KindID.GetString();
                var itemOrSubStr = ItemOrSub.GetString();
                var materialIDStr = MaterialID.GetString();
                var count = RetrunGoodsSingleValidCalcMatInfoCount(extrSource, companyID, ParentAreaID, kindIDStr, itemOrSubStr,
                    YearNo, SeasonID, SexID, materialIDStr);
                var result = PaperData<GoodsSingleValidCalcMatInfoModel.ShowModel>.Create(count, Size, Page);
                var group = RetrunGoodsSingleValidCalcMatInfoList(extrSource, companyID, ParentAreaID, kindIDStr, itemOrSubStr,
                    YearNo, SeasonID, SexID, materialIDStr, Page, Size).GroupBy(d => new
                    {
                        d.ParentAreaID,
                        d.MaterialID,
                        d.KindID,
                        d.KindName,
                        d.ItemOrSub,
                        d.SexID,
                        d.SexName,
                        d.OrderAttribID,
                        d.MaterialShortName,
                        d.RetailPrice,
                        d.StockQty,
                        d.AllCzpFlagNow,
                        d.AllCzpFlag,
                        d.DisplayQty,
                    });
                var list = group.Select(d => new GoodsSingleValidCalcMatInfoModel.ShowModel
                {
                    OutYear = DateTime.Now.Year,
                    ParentAreaID = d.Key.ParentAreaID,
                    MaterialID = d.Key.MaterialID,
                    KindID = d.Key.KindID,
                    KindName = d.Key.KindName,
                    ItemOrSub = d.Key.ItemOrSub,
                    SexID = d.Key.SexID,
                    SexName = d.Key.SexName,
                    OrderAttribID = d.Key.OrderAttribID,
                    MaterialShortName = d.Key.MaterialShortName,
                    RetailPrice = d.Key.RetailPrice,
                    StockQty = d.Key.StockQty,
                    AllCzpFlagNow = d.Key.AllCzpFlagNow,
                    AllCzpFlag = d.Key.AllCzpFlag,
                    DisplayQty = d.Key.DisplayQty,
                    AfterInfoList = d.Select(r => new GoodsSingleValidCalcAfterInfoModel.NeceModel
                    {
                        YearV = r.YearV,
                        WeekNo = r.WeekNo,
                        AfterReturnValidType = r.AfterReturnValidType,
                    }).OrderBy(r => Convert.ToInt32(r.YearV)).ThenBy(r => Convert.ToInt32(r.WeekNo)).ToList(),
                }).OrderBy(d => d.ParentAreaID).ThenBy(d => d.MaterialID).ThenBy(d => d.KindID).ThenBy(d => d.ItemOrSub).ThenBy(d => d.SexID).ThenBy(d => d.OrderAttribID);
                result.Items = list.ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// <summary>
        /// 返回单品有效计算货号信息
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="ItemOrSub"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonName"></param>
        /// <param name="SexID"></param>
        /// <param name="MaterialIDStr"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-02
        public IList<GoodsSingleValidCalcMatInfoModel.ListModel> RetrunGoodsSingleValidCalcMatInfoList(
            string ExtrSource, string CompanyID, string ParentAreaID, string KindID, string ItemOrSub, string YearNo,
            string SeasonName, string SexID, string MaterialIDStr, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        afterInfo.YearV ,
        afterInfo.WeekNo ,
        afterInfo.AfterReturnValidType
FROM    ( SELECT    table1.ExtrSource ,
                    table1.CompanyID ,
                    table1.ParentAreaID ,
                    table1.MaterialID ,
                    table1.MaterialShortName ,
                    table1.KindID ,
                    table1.ItemOrSub ,
                    table1.SexID ,
                    table1.OrderAttribID ,
                    table1.RetailPrice ,
                    table1.StockQty ,
                    table1.StockAmount ,
                    table1.AllCzpFlagNow ,
                    table1.AllCzpFlag ,
                    table1.DisplayQty ,
                    table1.SexName ,
                    table1.KindName
          FROM      ( SELECT    matInfo.* ,
                                sex.SexName ,
                                kind.KindName ,
                                ROW_NUMBER() OVER ( ORDER BY matInfo.ParentAreaID , matInfo.YearV , matInfo.WeekNo , matInfo.MaterialID, matInfo.KindID , matInfo.ItemOrSub, matInfo.OrderAttribID, matInfo.SexID ) AS Row
                      FROM      ( SELECT    *
                                  FROM      ngoods.ngoods_Single_Valid_Calc_MatInfo
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                            {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")
                    }
                                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                            {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND ItemOrSub in {ItemOrSub}")}
                                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                            {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND MaterialID IN {MaterialIDStr}")
                    }
                                            AND YearV = YEAR(CURRENT_DATE)
                                            AND WeekNo = ( SELECT
                                                              Weekv
                                                           FROM
                                                              ndw.ndw_CalendarDays
                                                           WHERE
                                                              GregorianDay::date = CURRENT_DATE
                                                              AND ExtrSource = '{ExtrSource}'
                                                              AND CompanyID = '{CompanyID}'
                                                         )
                                ) matInfo
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Material2
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND MaterialID IN {MaterialIDStr}")
                    }
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                                    {(string.IsNullOrEmpty(YearNo) ? "" : $"AND YearNo = {YearNo}")}
                                                    {
                        (string.IsNullOrEmpty(SeasonName) ? "" : $"AND SeasonName = '{SeasonName}'")
                    }
                                           ) material ON matInfo.ExtrSource = material.ExtrSource
                                                         AND matInfo.MaterialID = material.MaterialID
                                                         AND matInfo.KindID = material.KindID
                                                         AND matInfo.SexID = material.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Sex
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                           ) sex ON matInfo.ExtrSource = sex.ExtrSource
                                                     AND matInfo.SexID = sex.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                           ) kind ON matInfo.ExtrSource = kind.ExtrSource
                                                     AND matInfo.KindID = kind.KindID
                      WHERE     matInfo.ExtrSource = '{ExtrSource}'
                                AND matInfo.CompanyID = '{CompanyID}'
                                {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND matInfo.ParentAreaID = '{ParentAreaID}'")
                    }
                                {(string.IsNullOrEmpty(KindID) ? "" : $"AND matInfo.KindID in {KindID}")}
                                {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND matInfo.ItemOrSub in {ItemOrSub}")}
                                {(string.IsNullOrEmpty(SexID) ? "" : $"AND matInfo.SexID = '{SexID}'")}
                                {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND matInfo.MaterialID IN {MaterialIDStr}")
                    }
                                {(string.IsNullOrEmpty(YearNo) ? "" : $"AND material.YearNo = {YearNo}")}
                                {(string.IsNullOrEmpty(SeasonName) ? "" : $"AND material.SeasonName = '{SeasonName}'")}
                      GROUP BY  matInfo.ExtrSource ,
                                matInfo.CompanyID ,
                                matInfo.YearV ,
                                matInfo.WeekNo ,
                                matInfo.ParentAreaID ,
                                matInfo.MaterialID ,
                                matInfo.MaterialShortName ,
                                matInfo.KindID ,
                                matInfo.ItemOrSub ,
                                matInfo.SexID ,
                                matInfo.OrderAttribID ,
                                matInfo.RetailPrice ,
                                matInfo.StockQty ,
                                matInfo.StockAmount ,
                                matInfo.AllCzpFlagNow ,
                                matInfo.AllCzpFlag ,
                                matInfo.DisplayQty ,
                                matInfo.EtlDate ,
                                sex.SexName ,
                                kind.KindName 
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  table1.*
                    FROM    ( SELECT    *
                              FROM      ngoods.ngoods_Single_Valid_Calc_AfterInfo
                              WHERE     ExtrSource = '{ExtrSource}'
                                        AND CompanyID = '{CompanyID}'
                                        {(string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")}
                                        {(string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND MaterialID IN {MaterialIDStr}")}
                            ) table1
                            INNER JOIN ( SELECT ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                         FROM   ndw.ndw_CalendarDays
                                         WHERE  GregorianDay::date >= CURRENT_DATE
                                                AND GregorianDay::date <= ( CURRENT_DATE + ( 7 * 12 ) )
                                                AND ExtrSource = '{ExtrSource}'
                                                AND CompanyID = '{CompanyID}'
                                         GROUP BY ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                       ) table2 ON table1.ExtrSource = table2.ExtrSource
                                                   AND table1.CompanyID = table2.CompanyID
                                                   AND table1.YearV = table2.YearV
                                                   AND table1.WeekNo = table2.Weekv
                  ) afterInfo ON table2.ExtrSource = afterInfo.ExtrSource
                                 AND table2.CompanyID = afterInfo.CompanyID
                                 AND table2.ParentAreaID = afterInfo.ParentAreaID
                                 AND table2.MaterialID = afterInfo.MaterialID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsSingleValidCalcMatInfoModel.ListModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回单品有效计算货号条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="ItemOrSub"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonName"></param>
        /// <param name="SexID"></param>
        /// <param name="MaterialIDStr"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-02
        public int RetrunGoodsSingleValidCalcMatInfoCount(string ExtrSource, string CompanyID, string ParentAreaID, string KindID, string ItemOrSub,
            string YearNo, string SeasonName, string SexID, string MaterialIDStr)
        {
            try
            {
                var sql = $@"SELECT    Count(1)
          FROM      ( SELECT    matInfo.* ,
                                sex.SexName ,
                                kind.KindName ,
                                ROW_NUMBER() OVER ( ORDER BY matInfo.ParentAreaID , matInfo.YearV , matInfo.WeekNo , matInfo.MaterialID, matInfo.KindID , matInfo.ItemOrSub, matInfo.OrderAttribID, matInfo.SexID ) AS Row
                      FROM      ( SELECT    *
                                  FROM      ngoods.ngoods_Single_Valid_Calc_MatInfo
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                            {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")
                    }
                                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                            {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND ItemOrSub in {ItemOrSub}")}
                                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                            {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND MaterialID IN {MaterialIDStr}")
                    }
                                            AND YearV = YEAR(CURRENT_DATE)
                                            AND WeekNo = ( SELECT
                                                              Weekv
                                                           FROM
                                                              ndw.ndw_CalendarDays
                                                           WHERE
                                                              GregorianDay::date = CURRENT_DATE
                                                              AND ExtrSource = '{ExtrSource}'
                                                              AND CompanyID = '{CompanyID}'
                                                         )
                                ) matInfo
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Material2
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND MaterialID IN {MaterialIDStr}")
                    }
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                                    {(string.IsNullOrEmpty(YearNo) ? "" : $"AND YearNo = {YearNo}")}
                                                    {
                        (string.IsNullOrEmpty(SeasonName) ? "" : $"AND SeasonName = '{SeasonName}'")
                    }
                                           ) material ON matInfo.ExtrSource = material.ExtrSource
                                                         AND matInfo.MaterialID = material.MaterialID
                                                         AND matInfo.KindID = material.KindID
                                                         AND matInfo.SexID = material.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Sex
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                           ) sex ON matInfo.ExtrSource = sex.ExtrSource
                                                     AND matInfo.SexID = sex.SexID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID in {KindID}")}
                                           ) kind ON matInfo.ExtrSource = kind.ExtrSource
                                                     AND matInfo.KindID = kind.KindID
                      WHERE     matInfo.ExtrSource = '{ExtrSource}'
                                AND matInfo.CompanyID = '{CompanyID}'
                                {
                        (string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND matInfo.ParentAreaID = '{ParentAreaID}'")
                    }
                                {(string.IsNullOrEmpty(KindID) ? "" : $"AND matInfo.KindID in {KindID}")}
                                {(string.IsNullOrEmpty(ItemOrSub) ? "" : $"AND matInfo.ItemOrSub in {ItemOrSub}")}
                                {(string.IsNullOrEmpty(SexID) ? "" : $"AND matInfo.SexID = '{SexID}'")}
                                {
                        (string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND matInfo.MaterialID IN {MaterialIDStr}")
                    }
                                {(string.IsNullOrEmpty(YearNo) ? "" : $"AND material.YearNo = {YearNo}")}
                                {(string.IsNullOrEmpty(SeasonName) ? "" : $"AND material.SeasonName = '{SeasonName}'")}
                      GROUP BY  matInfo.ExtrSource ,
                                matInfo.CompanyID ,
                                matInfo.YearV ,
                                matInfo.WeekNo ,
                                matInfo.ParentAreaID ,
                                matInfo.MaterialID ,
                                matInfo.MaterialShortName ,
                                matInfo.KindID ,
                                matInfo.ItemOrSub ,
                                matInfo.SexID ,
                                matInfo.OrderAttribID ,
                                matInfo.RetailPrice ,
                                matInfo.StockQty ,
                                matInfo.StockAmount ,
                                matInfo.AllCzpFlagNow ,
                                matInfo.AllCzpFlag ,
                                matInfo.DisplayQty ,
                                matInfo.EtlDate ,
                                sex.SexName ,
                                kind.KindName 
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-02
        [MustLogin]
        [Description("单品有效计算回货计算")]
        [ReturnType(typeof(IList<GoodsSingleValidCalcAfterInfoModel.ListModel>))]
        public ApiResult GetGoodsSingleValidCalcAfterInfoList([ParamDescription("商品区域")] string ParentAreaID, [ParamDescription("货号")] string MaterialID)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (string.IsNullOrEmpty(ParentAreaID) || string.IsNullOrEmpty(MaterialID))
                {
                    return Error("请选择必要参数!");
                }
                var dt = ReturnGoodsSingleValidCalcAfterInfoList(extrSource, companyID, ParentAreaID, MaterialID);
                var list = ModelConvertHelper<GoodsSingleValidCalcAfterInfoModel.ListModel>.ConvertToModel(dt)
                    .OrderBy(r => Convert.ToInt32(r.YearV)).ThenBy(r => Convert.ToInt32(r.WeekNo));
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// <summary>
        /// 返回单品有效计算回货计算
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="MaterialID"></param>
        /// <param name="YearV"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-02
        public DataTable ReturnGoodsSingleValidCalcAfterInfoList(string ExtrSource, string CompanyID, string ParentAreaID, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  table1.*
                    FROM    ( SELECT    *
                              FROM      ngoods.ngoods_Single_Valid_Calc_AfterInfo
                              WHERE     ExtrSource = '{ExtrSource}'
                                        AND CompanyID = '{CompanyID}'
                                        AND MaterialID = '{MaterialID}'
                                        {(string.IsNullOrEmpty(ParentAreaID) ? "" : $"AND ParentAreaID = '{ParentAreaID}'")}
                            ) table1
                            INNER JOIN ( SELECT ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                         FROM   ndw.ndw_CalendarDays
                                         WHERE  GregorianDay::date >= CURRENT_DATE
                                                AND GregorianDay::date <= ( CURRENT_DATE + ( 7 * 12 ) )
                                                AND ExtrSource = '{ExtrSource}'
                                                AND CompanyID = '{CompanyID}'
                                         GROUP BY ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                       ) table2 ON table1.ExtrSource = table2.ExtrSource
                                                   AND table1.CompanyID = table2.CompanyID
                                                   AND table1.YearV = table2.YearV
                                                   AND table1.WeekNo = table2.Weekv";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-02
        [MustLogin]
        [Description("单品有效计算")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult CalculateGoodsSingleValidCalc()
        {
            try
            {
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var cmdJudge = $"python /home/bi/scripts/python/etl/ndw/SP_Single_Valid_Calc_Monitor.py";
                var cmd = $"python /home/bi/scripts/python/etl/ndw/SP_Single_Valid_Calc.py  {dateTime}";
                var resJudge = ConnLinux(server, username, port, password, cmdJudge);
                if (resJudge.Contains("1")) return Error("当前有用户正在计算!");
                var result = ConnLinux(server, username, port, password, cmd);
                return result.Contains("success") ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("计算失败!");
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-11
        [MustLogin]
        [Description("单品有效计算")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CalculateGoodsSingleValidCalcByCSharp()
        {
            return null;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-16
        public DataTable ExportGoodsSingleValidCalc(string Keys)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var sql = $@"SELECT  mat.ParentAreaID ,
        kind.KindName ,
        mat.ItemOrSub ,
        mat.MaterialID ,
        sex.SexName ,
        mat.RetailPrice ,
        mat.OrderAttribID ,
        mat.AllCzpFlagNow ,
        mat.AllCzpFlag ,
        mat.DisplayQty ,
        aft.WeekNo ,
        aft.ShopCount ,
        aft.InitQty ,
        aft.ReturnQty ,
        aft.PlanSaleQty ,
        aft.BalanceQty ,
        aft.AfterReturnValidQty ,
        aft.AfterReturnValidTagAmount ,
        ( CASE aft.AfterReturnValidType
            WHEN '0' THEN '有效'
            WHEN '1' THEN '半有效'
            ELSE '无效'
          END ) AS AfterReturnValidType
FROM    ( SELECT    *
          FROM      ngoods.ngoods_Single_Valid_Calc_MatInfo
          WHERE     ExtrSource = '{extrSource}'
                    AND CompanyID = '{companyID}'
                    AND YearV = YEAR(CURRENT_DATE)
        ) mat
        LEFT JOIN ( SELECT  table1.*
                    FROM    ( SELECT    *
                              FROM      ngoods.ngoods_Single_Valid_Calc_AfterInfo
                              WHERE     ExtrSource = '{extrSource}'
                                        AND CompanyID = '{companyID}'
                            ) table1
                            INNER JOIN ( SELECT ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                         FROM   ndw.ndw_CalendarDays
                                         WHERE  GregorianDay::date >= CURRENT_DATE
                                                AND GregorianDay::date <= ( CURRENT_DATE + ( 7 * 12 ) )
                                                AND ExtrSource = '{extrSource}'
                                                AND CompanyID = '{companyID}'
                                         GROUP BY ExtrSource ,
                                                CompanyID ,
                                                YearV ,
                                                Weekv
                                       ) table2 ON table1.ExtrSource = table2.ExtrSource
                                                   AND table1.CompanyID = table2.CompanyID
                                                   AND table1.YearV = table2.YearV
                                                   AND table1.WeekNo = table2.Weekv
                   ) aft ON mat.ExtrSource = aft.ExtrSource
                            AND mat.CompanyID = aft.CompanyID
                            AND mat.ParentAreaID = aft.ParentAreaID
                            AND mat.MaterialID = aft.MaterialID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Material2
                     WHERE  ExtrSource = '{extrSource}'
                   ) mater ON mat.ExtrSource = mater.ExtrSource
                              AND mat.MaterialID = mater.MaterialID
                              AND mat.KindID = mater.KindID
                              AND mat.SexID = mater.SexID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Kind
                     WHERE  ExtrSource = '{extrSource}'
                   ) kind ON mat.ExtrSource = kind.ExtrSource
                        AND mat.KindID = kind.KindID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Sex
                     WHERE  ExtrSource = '{extrSource}'
                   ) sex ON mat.ExtrSource = sex.ExtrSource
                        AND mat.SexID = sex.SexID
WHERE 1=1
        {Keys}
ORDER BY mat.ParentAreaID ,
        mat.KindID ,
        mat.ItemOrSub ,
        mat.MaterialID ,
        mat.SexID ,
        mat.OrderAttribID ,
        aft.WeekNo::int";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

    }
}
